package com.ruoyi.biz.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.ruoyi.biz.domain.BizInvoice;
import com.ruoyi.biz.domain.BizProject;
import com.ruoyi.biz.domain.BizReceipt;
import com.ruoyi.biz.domain.BizTrustee;
import com.ruoyi.biz.mapper.BizInvoiceMapper;
import com.ruoyi.biz.mapper.BizReceiptMapper;
import com.ruoyi.biz.model.excel.BizInvoiceExportVO;
import com.ruoyi.biz.model.excel.StatCzExcel;
import com.ruoyi.biz.model.excel.StatRiskExcel;
import com.ruoyi.biz.model.req.StatReq;
import com.ruoyi.biz.model.resp.BizInvoiceResp;
import com.ruoyi.biz.utils.ReqUtils;
import com.ruoyi.common.core.domain.entity.SysDictData;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.DictUtils;
import com.ruoyi.common.utils.poi.ExcelUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.ZoneId;
import java.util.*;
import java.util.stream.Collectors;

import javax.servlet.http.HttpServletResponse;

/**
 * @author tianl
 * @date 2025/1/3
 * @description:
 */
@Service
public class StatExportService {
    @Autowired
    private BizProjectService bizProjectService;
    @Autowired
    private BizReceiptMapper bizReceiptMapper;
    @Autowired
    private BizInvoiceMapper bizInvoiceMapper;
    @Autowired
    private BizTrusteeService bizTrusteeService;

    public List<List<String>> getRiskHead(Integer year) {
        List<List<String>> head = new ArrayList<>();
        head.add(Arrays.asList("公司名称"));
        head.add(Arrays.asList(year + ""));
        head.add(Arrays.asList(year - 1 + ""));
        head.add(Arrays.asList(year - 2 + ""));
        return head;
    }

    public List<StatRiskExcel> exportRisk(Integer year) {
        List<StatRiskExcel> result = new ArrayList<>();
        
        // 获取所有受托公司
        List<BizTrustee> allTrustees = bizTrusteeService.list(new BizTrustee());
        Map<String, BizTrustee> trusteeMap = allTrustees.stream()
                .collect(Collectors.toMap(BizTrustee::getCompanyName, t -> t));
        
        // 获取项目列表
        List<BizProject> projectList = bizProjectService.listStat(new StatReq());
        if (CollectionUtils.isEmpty(projectList)) {
            return result;
        }
        
        // 获取发票数据
        List<Long> projectIds = projectList.stream().map(BizProject::getId).collect(Collectors.toList());
        List<BizInvoiceResp> invoiceList = bizInvoiceMapper.listByProjectIds(projectIds);
        Map<String, List<BizInvoiceResp>> invoiceMap = invoiceList.stream()
                .collect(Collectors.groupingBy(BizInvoiceResp::getInvoiceSubject));
        
        // 计算每个公司的开票金额并排序
        Map<String, BigDecimal> companyAmountMap = invoiceList.stream()
                .filter(item -> {
                    LocalDate itemDate = item.getInvoiceTime().toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
                    return itemDate.getYear() == year;
                })
                .collect(Collectors.groupingBy(
                        BizInvoiceResp::getInvoiceSubject,
                        Collectors.mapping(BizInvoiceResp::getInvoiceMoney, Collectors.reducing(BigDecimal.ZERO, BigDecimal::add))
                ));
        
        // 按开票金额降序排序公司名称
        List<String> sortedCompanies = companyAmountMap.entrySet().stream()
                .sorted(Map.Entry.<String, BigDecimal>comparingByValue().reversed()
                        .thenComparing(Map.Entry.comparingByKey()))
                .map(Map.Entry::getKey)
                .collect(Collectors.toList());
        
        // 添加所有受托公司的数据，按照开票金额排序
        for (String companyName : sortedCompanies) {
            StatRiskExcel statRiskResp = new StatRiskExcel();
            statRiskResp.setName(companyName);
            
            List<BizInvoiceResp> companyInvoices = invoiceMap.get(companyName);
            if (CollectionUtils.isEmpty(companyInvoices)) {
                // 如果没有开票记录，设置金额为0
                statRiskResp.setNum1(BigDecimal.ZERO);
                statRiskResp.setNum2(BigDecimal.ZERO);
                statRiskResp.setNum3(BigDecimal.ZERO);
            } else {
                // 计算各年份的开票金额
                BigDecimal num1 = companyInvoices.stream()
                        .filter(item -> {
                            LocalDate itemDate = item.getInvoiceTime().toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
                            return itemDate.getYear() == year;
                        })
                        .map(BizInvoiceResp::getInvoiceMoney)
                        .reduce(BigDecimal.ZERO, BigDecimal::add);
                        
                BigDecimal num2 = companyInvoices.stream()
                        .filter(item -> {
                            LocalDate itemDate = item.getInvoiceTime().toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
                            return itemDate.getYear() == year - 1;
                        })
                        .map(BizInvoiceResp::getInvoiceMoney)
                        .reduce(BigDecimal.ZERO, BigDecimal::add);
                        
                BigDecimal num3 = companyInvoices.stream()
                        .filter(item -> {
                            LocalDate itemDate = item.getInvoiceTime().toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
                            return itemDate.getYear() == year - 2;
                        })
                        .map(BizInvoiceResp::getInvoiceMoney)
                        .reduce(BigDecimal.ZERO, BigDecimal::add);
                        
                statRiskResp.setNum1(num1);
                statRiskResp.setNum2(num2);
                statRiskResp.setNum3(num3);
            }
            result.add(statRiskResp);
        }
        
        // 添加未开票的公司（金额为0）
        for (String companyName : trusteeMap.keySet()) {
            if (!sortedCompanies.contains(companyName)) {
                StatRiskExcel statRiskResp = new StatRiskExcel();
                statRiskResp.setName(companyName);
                statRiskResp.setNum1(BigDecimal.ZERO);
                statRiskResp.setNum2(BigDecimal.ZERO);
                statRiskResp.setNum3(BigDecimal.ZERO);
                result.add(statRiskResp);
            }
        }

        // 计算合计
        BigDecimal num1Total = result.stream().map(StatRiskExcel::getNum1).reduce(BigDecimal.ZERO, BigDecimal::add);
        BigDecimal num2Total = result.stream().map(StatRiskExcel::getNum2).reduce(BigDecimal.ZERO, BigDecimal::add);
        BigDecimal num3Total = result.stream().map(StatRiskExcel::getNum3).reduce(BigDecimal.ZERO, BigDecimal::add);
        
        StatRiskExcel totalRow = new StatRiskExcel();
        totalRow.setName("合计");
        totalRow.setNum1(num1Total);
        totalRow.setNum2(num2Total);
        totalRow.setNum3(num3Total);
        result.add(totalRow);
        
        return result;
    }

    public List<StatCzExcel> exportCz(Integer year) {
        StatReq req = ReqUtils.getReq(year);
        List<StatCzExcel> result = new ArrayList<>();
        List<BizProject> projectList = bizProjectService.listStat(req);
        if (CollectionUtils.isEmpty(projectList)) {
            StatCzExcel resp = new StatCzExcel();
            resp.setMonth("合计");
            resp.setMonthProjectNum(0);
            resp.setMonthAmount(BigDecimal.ZERO);
            resp.setMonthRealAmount(BigDecimal.ZERO);
            resp.setMonthReceiptAmount(BigDecimal.ZERO);
            resp.setMonthUnReceiptAmount(BigDecimal.ZERO);
            resp.setProportion("0%");
            result.add(resp);
            return result;
        }

        List<Long> projectIds = projectList.stream().map(BizProject::getId).collect(Collectors.toList());

        List<BizReceipt> receiptList = bizReceiptMapper.listEntityByProjectIds(projectIds);
        Map<Integer, List<BizReceipt>> receiptByMonth = receiptList.stream()
                .filter(r -> r.getReceiptTime() != null)
                .collect(Collectors.groupingBy(re -> {
                    Calendar calendar = Calendar.getInstance();
                    calendar.setTime(re.getReceiptTime());
                    return calendar.get(Calendar.MONTH) + 1; // 获取月份，注意月份从0开始，所以要加1
                }));
        Map<Integer, List<BizProject>> groupedByMonth = projectList.stream()
                .filter(r -> r.getSignTime() != null)
                .collect(Collectors.groupingBy(project -> {
                    Calendar calendar = Calendar.getInstance();
                    calendar.setTime(project.getSignTime());
                    return calendar.get(Calendar.MONTH) + 1; // 获取月份，注意月份从0开始，所以要加1
                }));
        BigDecimal totalProjectNum = BigDecimal.valueOf(projectList.size());
        Integer monthProjectNum = 0;
        BigDecimal monthAmount = BigDecimal.ZERO;
        BigDecimal monthRealAmount = BigDecimal.ZERO;
        BigDecimal monthReceiptAmount = BigDecimal.ZERO;
        BigDecimal monthUnReceiptAmount = BigDecimal.ZERO;
        for (int i = 1; i <= 12; i++) {
            BigDecimal tmpTotal = BigDecimal.ZERO;
            BigDecimal tmpRealAmount = BigDecimal.ZERO;
            StatCzExcel resp = new StatCzExcel();
            resp.setMonth(i + "月");
            int projectNum = 0;
            if (groupedByMonth.containsKey(i)) {
                tmpTotal = groupedByMonth.get(i).stream()
                        .filter(r -> r.getTotalAmount() != null)
                        .map(BizProject::getTotalAmount)
                        .reduce(BigDecimal.ZERO, BigDecimal::add);
                tmpRealAmount = groupedByMonth.get(i).stream()
                        .filter(r -> r.getRealAmount() != null)
                        .map(BizProject::getRealAmount)
                        .reduce(BigDecimal.ZERO, BigDecimal::add);
                projectNum = groupedByMonth.get(i).size();
            }
            resp.setMonthProjectNum(projectNum);
            monthProjectNum += resp.getMonthProjectNum();
            resp.setMonthAmount(tmpTotal.divide(BigDecimal.valueOf(10000), 2, RoundingMode.HALF_UP));
            monthAmount = monthAmount.add(resp.getMonthAmount());
            resp.setMonthRealAmount(tmpRealAmount.divide(BigDecimal.valueOf(10000), 2, RoundingMode.HALF_UP));
            monthRealAmount = monthRealAmount.add(resp.getMonthRealAmount());
            BigDecimal tmpReceipt = BigDecimal.ZERO;
            if (receiptByMonth.containsKey(i)) {
                tmpReceipt = receiptByMonth.get(i).stream()
                        .filter(r -> r.getReceiptMoney() != null)
                        .map(BizReceipt::getReceiptMoney)
                        .reduce(BigDecimal.ZERO, BigDecimal::add);
            }
            resp.setMonthReceiptAmount(tmpReceipt.divide(BigDecimal.valueOf(10000), 2, RoundingMode.HALF_UP));
            monthReceiptAmount = monthReceiptAmount.add(resp.getMonthReceiptAmount());
            resp.setMonthUnReceiptAmount(tmpTotal.subtract(tmpReceipt).divide(BigDecimal.valueOf(10000), 2, RoundingMode.HALF_UP));
            monthUnReceiptAmount = monthUnReceiptAmount.add(resp.getMonthUnReceiptAmount());
            resp.setProportion(BigDecimal.valueOf(projectNum).divide(totalProjectNum, 2, RoundingMode.HALF_UP).multiply(BigDecimal.valueOf(100)) + "%");
            result.add(resp);
        }
        StatCzExcel resp = new StatCzExcel();
        resp.setMonth("合计");
        resp.setMonthProjectNum(monthProjectNum);
        resp.setMonthAmount(monthAmount);
        resp.setMonthRealAmount(monthRealAmount);
        resp.setMonthReceiptAmount(monthReceiptAmount);
        resp.setMonthUnReceiptAmount(monthUnReceiptAmount);
        resp.setProportion("100%");
        result.add(resp);
        return result;
    }

    public void exportRiskWithCompanySheets(Integer year, HttpServletResponse response) {
        try {
            // 获取原有的风险统计数据
            List<StatRiskExcel> riskData = exportRisk(year);
            
            // 获取指定年份的开票信息
            List<BizInvoiceResp> yearInvoices = bizInvoiceMapper.listByYear(year);
            
            // 计算每个公司的开票总额并排序
            Map<String, BigDecimal> companyTotalAmount = yearInvoices.stream()
                    .collect(Collectors.groupingBy(
                        BizInvoiceResp::getInvoiceSubject,
                        Collectors.reducing(BigDecimal.ZERO,
                            BizInvoiceResp::getInvoiceMoney,
                            BigDecimal::add)
                    ));
            
            Map<String, List<BizInvoiceResp>> companyInvoices = yearInvoices.stream()
                    .collect(Collectors.groupingBy(BizInvoiceResp::getInvoiceSubject));
            
            // 按开票总额排序
            List<Map.Entry<String, List<BizInvoiceResp>>> sortedCompanies = companyInvoices.entrySet()
                    .stream()
                    .sorted((e1, e2) -> companyTotalAmount.get(e2.getKey())
                            .compareTo(companyTotalAmount.get(e1.getKey())))
                    .collect(Collectors.toList());
            
            // 设置响应头
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("开票情况-" + year, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            
            // 创建ExcelWriter，设置自动列宽
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .build();
            
            try {
                // 添加第一个sheet（原有的风险统计）
                WriteSheet riskSheet = EasyExcel.writerSheet(0, "风险统计")
                        .head(getRiskHead(year))
                        .build();
                excelWriter.write(riskData, riskSheet);
                
                // 为每个受托公司添加一个sheet（按开票金额排序）
                int sheetIndex = 1;
                for (Map.Entry<String, List<BizInvoiceResp>> entry : sortedCompanies) {
                    String companyName = entry.getKey();
                    // 对发票列表按开票时间和创建时间排序
                    List<BizInvoiceResp> invoices = entry.getValue().stream()
                            .sorted(Comparator.comparing(BizInvoiceResp::getInvoiceTime)
                                    .thenComparing(BizInvoiceResp::getCreateTime))
                            .collect(Collectors.toList());
                    BigDecimal totalAmount = companyTotalAmount.get(companyName);
                    
                    // sheet名称包含公司名称和总金额
                    String sheetName = String.format("%s(%.2f万)", companyName, 
                            totalAmount.divide(BigDecimal.valueOf(10000), 2, RoundingMode.HALF_UP));
                    
                    // 转换为导出VO
                    List<BizInvoiceExportVO> exportVOList = invoices.stream().map(invoice -> {
                        BizInvoiceExportVO vo = new BizInvoiceExportVO();
                        vo.setProjectName(invoice.getProjectName());
                        // 格式化开票日期为年月日
                        vo.setInvoiceTime(DateUtils.parseDateToStr(DateUtils.YYYY_MM_DD, invoice.getInvoiceTime()));
                        // 转换发票类型字典
                        vo.setInvoiceType(DictUtils.getDictLabel("invoice_type", invoice.getInvoiceType()));
                        vo.setInvoiceSubject(invoice.getInvoiceSubject());
                        vo.setHeaderTitle(invoice.getHeaderTitle());
                        vo.setInvoiceMoney(invoice.getInvoiceMoney());
                        vo.setInvoiceNo(invoice.getInvoiceNo());
                        // 转换应税服务名称字典
                        vo.setInvoiceService(DictUtils.getDictLabel("invoice_service", invoice.getInvoiceService()));
                        vo.setInvoiceUserName(invoice.getInvoiceUserName());
                        vo.setUserName(invoice.getUserName());
                        // 格式化创建时间为年月日时分秒
                        vo.setCreateTime(DateUtils.parseDateToStr(DateUtils.YYYY_MM_DD_HH_MM_SS, invoice.getCreateTime()));
                        vo.setCompanyName(invoice.getCompanyName());
                        vo.setDepositBank(invoice.getDepositBank());
                        vo.setCardNumber(invoice.getCardNumber());
                        return vo;
                    }).collect(Collectors.toList());
                    
                    WriteSheet companySheet = EasyExcel.writerSheet(sheetIndex++, sheetName)
                            .head(BizInvoiceExportVO.class)
                            .build();
                    excelWriter.write(exportVOList, companySheet);
                }
            } finally {
                if (excelWriter != null) {
                    excelWriter.finish();
                }
            }
        } catch (Exception e) {
            throw new RuntimeException("导出Excel失败", e);
        }
    }
}
